package com.joker.aigc.tools.domian.po;

import cn.hutool.core.collection.CollectionUtil;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.Statements;
import net.sf.jsqlparser.statement.create.index.CreateIndex;
import net.sf.jsqlparser.statement.create.table.CreateTable;
import net.sf.jsqlparser.util.TablesNamesFinder;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @Desciption:
 * @Author: feixiang.li
 * @date: 2024-08-01 13:45
 **/
@Slf4j
@Data
public class TableParsePO {

    /**
     * 表名
     */
    private Set<String> tableNameList = new HashSet<>();
    /**
     * 表注释
     */
    private List<String> comment = new ArrayList<>();
    /**
     * 表索引
     */
    private Map<String, Set<String>> indexMaps = new HashMap<>();
    /**
     * 解析后的索引
     */
    private Map<String, Set<String>> parseIndex = new HashMap<>();
    /**
     * 创建表语句
     */
    private Map<String, String> createTableSql = new HashMap<>();

    /**
     * 需要添加的tableName;
     */
    private Set<String> needAddTableName = new HashSet<>();

    /**
     * 获取tableName
     *
     * @param sql sql语句
     * @return
     */
    public static List<String> getTableNameListByParse(String sql) {
        //解析数据库获取表名
        Set<String> tableList = new HashSet<>();
        try {
            Statements statements = CCJSqlParserUtil.parseStatements(sql);
            log.info("Statement->{}", statements);
            // 创建表名发现者对象
            TablesNamesFinder tablesNamesFinder = new TablesNamesFinder();
            // 获取到表名列表
            if (CollectionUtil.isNotEmpty(statements)) {
                statements.forEach(statement -> {
                    //判断ddl类型是否是create类型 ，如果想获取ddl所有语句的表名，这条代码去掉
                    if (statement instanceof CreateTable) {
                        Set<String> tableList1 = tablesNamesFinder.getTables(statement);
                        if (CollectionUtil.isNotEmpty(tableList1)) {
                            tableList.addAll(tableList1);
                        }
                    }
                });
            }
        } catch (Exception e) {
            e.printStackTrace();
            return new ArrayList<>(tableList);
        }
        return new ArrayList<>(tableList);
    }

    /**
     * 获取表名字
     *
     * @param sqlStatements
     * @return
     */
    public static List<String> getTableNameListByPattern(String sqlStatements) {
        Set<String> tableNames = new HashSet<>();

        // Regex pattern to match CREATE TABLE statements and capture table names
        Pattern pattern = Pattern.compile("(?i)CREATE TABLE\\s+(\\w+)", Pattern.CASE_INSENSITIVE);

        Matcher matcher = pattern.matcher(sqlStatements);
        while (matcher.find()) {
            String tableName = matcher.group(1);
            tableNames.add(tableName);
        }

        return new ArrayList<>(tableNames);
    }

    public static String getTableName(String sql) {
        try {

            Statement parse = CCJSqlParserUtil.parse(sql);
            if (parse instanceof CreateIndex) {
                return ((CreateIndex) parse).getTable().getName();
            }
            if (parse instanceof CreateTable) {
                return ((CreateTable) parse).getTable().getName();
            }
            // 创建表名发现者对象
            TablesNamesFinder tablesNamesFinder = new TablesNamesFinder();
            // 获取到表名列表
            Set<String> tableNameList = tablesNamesFinder.getTables(parse);
            if (CollectionUtil.isNotEmpty(tableNameList)) {
                return tableNameList.iterator().next();
            }
        } catch (Exception e) {
            e.printStackTrace();
            List<String> tableNameListByParse = getTableNameListByParse(sql);
            if (CollectionUtil.isNotEmpty(tableNameListByParse)) {
                return tableNameListByParse.iterator().next();
            }
            return "";
        }
        return "";
    }

    /**
     * 获取2个大的sql中，没有的表的名
     *
     * @param sql1 比较大的sql。包含多个创建表的雨具
     * @param sql2 比较小的sql。包含多张sql语句
     * @return
     */
    public List<String> getNeedAddTable(String sql1, String sql2) {
        List<String> sql1TableNameSet = getTableNameListByPattern(sql1);
        List<String> sql2TableNameSet = getTableNameListByPattern(sql2);
        sql1TableNameSet.removeAll(sql2TableNameSet);
        needAddTableName.addAll(sql1TableNameSet);
        return sql1TableNameSet;
    }


    public static String convertCreateIndexToAlterTable(String createIndexStatement) {
        // 正则表达式匹配 create index 语句，不区分大小写
        Pattern pattern = Pattern.compile("(create|CREATE)\\s+(index|INDEX)\\s+(\\w+)\\s+(on|ON)\\s+(\\w+)\\s*\\(([^)]+)\\)", Pattern.CASE_INSENSITIVE);
        Matcher matcher = pattern.matcher(createIndexStatement);

        if (matcher.find()) {
            String indexName = matcher.group(3);
            String tableName = matcher.group(5);
            String columns = matcher.group(6);
            // 构建 ALTER TABLE 语句
            return "ALTER TABLE " + tableName + " ADD INDEX " + indexName + " (" + columns + ")";
        }
        return createIndexStatement; // 或者抛出异常，表示无法解析语句
    }

    public static List<String> splitSQLStatements(String sqlStatements) {
        List<String> sqlList = new ArrayList<>();

        Pattern pattern = Pattern.compile(
                "(?i)(?<=;)|" +                      // Match semicolon (case insensitive)
                        "(?:\"(?:\\\\\"|[^\"])*\")|" +      // Match double quoted strings
                        "(?:'(?:\\\\'|[^'])*')"             // Match single quoted strings
        );

        Matcher matcher = pattern.matcher(sqlStatements);
        int lastMatchEnd = 0;
        boolean inQuote = false;

        while (matcher.find()) {
            String match = matcher.group();
            if (match.equals(";") && !inQuote) {
                String statement = sqlStatements.substring(lastMatchEnd, matcher.end()).trim();
                if (!statement.isEmpty()) {
                    sqlList.add(statement);
                }
                lastMatchEnd = matcher.end();
            } else if (match.startsWith("\"") || match.startsWith("'")) {
                inQuote = !inQuote;
            }
        }

        // Add the last remaining SQL statement if exists
        if (lastMatchEnd < sqlStatements.length()) {
            String statement = sqlStatements.substring(lastMatchEnd).trim();
            if (!statement.isEmpty()) {
                sqlList.add(statement);
            }
        }

        return sqlList;
    }

    /**
     * @param sql1
     * @param sql2
     * @return
     */
    public String addSql(String sql1, String sql2) {

        List<String> needAddTable = getNeedAddTable(sql1, sql2);

        if (CollectionUtil.isEmpty(needAddTable)) {
            return "";
        }
        // step 1: 首先开始解析
        List<String> list1 = new ArrayList<>(Arrays.asList(sql1.split(";\r")));
        List<String> list2 = new ArrayList<>(Arrays.asList(sql2.split(";\r")));

        // step 2: 移除相同的表结构
        list1.removeAll(list2);

        // step 3: 添加需要的表结构
        list1.forEach(this::addSingleSqlByCheck);
        // step 4: 凭借字符串
        return getCreateTableSql();
    }

    /**
     * 一条语句的创建
     *
     * @param sql 创建语句
     */
    public void addSingleSql(String sql) {
        // step 1: 解析表名字
        String tableName = getTableName(sql);
        // step 2: 判断是建表，还是建立索引
        if (sql.toLowerCase().contains("create table")) {
            // step 2.1 把建表语句放入其中
            createTableSql.put(tableName, sql);
            tableNameList.add(tableName);
        } else {
            Set<String> tableIndexList = indexMaps.getOrDefault(tableName, new HashSet<>());
            Set<String> tableIndexParseList = parseIndex.getOrDefault(tableName, new HashSet<>());
            // step 3: 如果是建立索引。则 把 create index
            String parseCreateIndexToAlterTable = convertCreateIndexToAlterTable(sql);
            tableIndexList.add(sql);
            tableIndexParseList.add(parseCreateIndexToAlterTable);
            // step 4: 添加到索引中
            indexMaps.put(tableName, tableIndexList);
            parseIndex.put(tableName, tableIndexParseList);
        }
    }

    /**
     * 校验check,查看表结构是否需要添加
     *
     * @param sql 需要添加的表结构
     */
    public void addSingleSqlByCheck(String sql) {
        // step 1: 解析表名字
        String tableName = getTableName(sql);
        // 如果不在需要添加的表结构里面，直接不添加
        if (CollectionUtil.isEmpty(needAddTableName) || !needAddTableName.contains(tableName)) {
            return;
        }
        // step 2: 判断是建表，还是建立索引
        if (sql.toLowerCase().contains("create table")) {
            // step 2.1 把建表语句放入其中
            createTableSql.put(tableName, sql);
            tableNameList.add(tableName);
        } else {
            Set<String> tableIndexList = indexMaps.getOrDefault(tableName, new HashSet<>());
            Set<String> tableIndexParseList = parseIndex.getOrDefault(tableName, new HashSet<>());
            // step 3: 如果是建立索引。则 把 create index
            String parseCreateIndexToAlterTable = convertCreateIndexToAlterTable(sql);
            tableIndexList.add(sql);
            tableIndexParseList.add(parseCreateIndexToAlterTable);
            // step 4: 添加到索引中
            indexMaps.put(tableName, tableIndexList);
            parseIndex.put(tableName, tableIndexParseList);
        }
    }

    public String getCreateIndexSql(Set<String> indexList) {

        if (CollectionUtil.isEmpty(indexList)) {
            return "";
        }
        // Use a regex pattern to capture the table name and index parts
        Pattern pattern = Pattern.compile("ALTER TABLE (\\w+) ADD INDEX (.*?)\\)", Pattern.CASE_INSENSITIVE);

        String tableName = null;
        StringBuilder combinedSql = new StringBuilder();

        boolean first = true;
        for (String sql : indexList) {
            Matcher matcher = pattern.matcher(sql);
            if (matcher.find()) {
                if (tableName == null) {
                    tableName = matcher.group(1);  // Extract table name
                    combinedSql.append("ALTER TABLE ").append(tableName);
                }
                String indexPart = "ADD INDEX " + matcher.group(2) + ")";
                if (first) {
                    combinedSql.append(" ").append(indexPart);
                    first = false;
                } else {
                    combinedSql.append(", ").append(indexPart);
                }
            }
        }
        return combinedSql.toString();
    }

    public String getCreateTableSql() {

        // step 1: 遍历创建表结构
        StringBuilder sb = new StringBuilder();
        // step 2: 添加表结构和索引结构
        for (String tableName : tableNameList) {
            sb.append(createTableSql.get(tableName)).append(";\r\n");
            // step 2: 遍历索引
            Set<String> indexList = parseIndex.getOrDefault(tableName, new HashSet<>());
            // 添加索引字符串
            if (!CollectionUtil.isEmpty(indexList)) {
                sb.append(getCreateIndexSql(indexList)).append(";\r\n");
            }
            parseIndex.remove(tableName);
        }

        // 把剩余索引结构添加进来。使用foreach实现
        parseIndex.forEach((key, value) -> {
            sb.append(getCreateIndexSql(value)).append(";\r\n");
        });
        return sb.toString();
    }

    // 源 sql 文件（测试环境）
    public static String sqlFileNameFrom = "zyq_test.sql";
    // 目标 sql 文件（正式环境）
    public static String sqlFileNameTo = "zyq_live.sql";

    public static void setSqlPath(String from, String to) {
        sqlFileNameFrom = from;
        sqlFileNameTo = to;
    }


    public static void printDifference() {
        String msg = checkFile();
        if (msg != null) {
            System.err.println(msg);
            return;
        }
        // 获取源 sql 文件的表字段信息
        Map<String, Map<String, String>> fromMap = getTableMap(sqlFileNameFrom);
        // 获取目标 sql 文件的表字段信息
        Map<String, Map<String, String>> toMap = getTableMap(sqlFileNameTo);
        // 比较两个 sql 文件的字段信息
        List<String> differenceList = getDifferenceList(fromMap, toMap);
        if (differenceList.isEmpty()) {
            System.out.println("表和字段无变化");
            return;
        }
        int size = differenceList.size();
        System.out.println("共变化 " + size + "处，具体如下：");
        for (int i = 0; i < size; i++) {
            System.out.println(String.format("【%s】%s", (i + 1), differenceList.get(i)));
        }
    }

    private static List<String> getDifferenceList(Map<String, Map<String, String>> fromMap,
                                                  Map<String, Map<String, String>> toMap) {
        List<String> list = new ArrayList<String>();
        Set<String> toTableSet = toMap.keySet();
        for (String tableName : toTableSet) {
            Map<String, String> fromFieldMap = fromMap.get(tableName);
            // 如果目标文件有该表，源文件没有该表，则表已被删除，冗余
            if (fromFieldMap == null) {
                list.add("多余表：" + tableName);
            }
            // 继续比较表的字段
            Map<String, String> toFieldMap = toMap.get(tableName);
            List<String> fieldList = getDifferenceFieldList(tableName, fromFieldMap, toFieldMap);
            if (!fieldList.isEmpty()) {
                list.addAll(fieldList);
            }
        }
        // 新增表
        Set<String> fromTableSet = fromMap.keySet();
        fromTableSet.removeAll(toTableSet);
        for (String tableName : fromTableSet) {
            list.add("新增表：" + tableName);
        }
        return list;
    }


    private static List<String> getDifferenceFieldList(String tableName, Map<String, String> fromMap,
                                                       Map<String, String> toMap) {
        List<String> list = new ArrayList<String>();
        Set<String> toFiledSet = toMap.keySet();
        for (String filedName : toFiledSet) {
            String fromFileType = fromMap.get(filedName);
            // 如果目标文件表有该字段，源文件表没有该字段，则该字段已被删除，冗余
            if (fromFileType == null) {
                list.add("%s 表多余字段：" + fromFileType);
                continue;
            }
            // 继续比较字段类型
            String toFileType = toMap.get(filedName);
            if (!fromFileType.equals(toFileType)) {
                list.add(String.format("%s 表字段变化：%s : %s -> %s", tableName, filedName, toFileType, fromFileType));
            }
        }
        // 新增字段
        Set<String> fromFiledSet = fromMap.keySet();
        fromFiledSet.removeAll(toFiledSet);
        for (String fromFiledName : fromFiledSet) {
            list.add(String.format("%s 表新增字段：%s : %s", tableName, fromFiledName, fromMap.get(fromFiledName)));
        }
        return list;
    }

    private static Map<String, Map<String, String>> getTableMap(String fileName) {
        String sqlFrom = getFileContent(fileName);
        Map<String, Map<String, String>> map = new HashMap<String, Map<String, String>>();
        // 读取 sql 文件的所有行
        String[] lines = sqlFrom.split("\r\n");
        String tableName = null;
        for (String line : lines) {
            String trimLine = line.trim();
            // 以【CREATE TABLE】为开头的，则为表名
            if (trimLine.startsWith("CREATE TABLE")) {
                // 然后去掉 CREATE TABLE、`、( 即可获取表名
                tableName = trimLine.replaceAll("CREATE TABLE|`|\\(", "").trim();
                map.put(tableName, new HashMap<String, String>());
                continue;
            }
            // 以 ` 开头的，则为字段
            if (trimLine.startsWith("`")) {
                // 第 1 个 ` 和第 2 个 ` 之间的则为字段名
                String fieldName = getSplintIndexValue(trimLine, "`", 1);
                // 按 [空格] 拆分的第2个为字段类型
                String fieldType = getSplintIndexValue(trimLine, " ", 2);
                map.get(tableName).put(fieldName, fieldType);
            }
        }
        return map;
    }

    private static String getSplintIndexValue(String s, String regex, int index) {
        String[] strs = s.split(regex);
        int idx = 0;
        for (String str : strs) {
            if (!str.isEmpty()) {
                idx++;
            }
            if (idx == index) {
                return str;
            }
        }
        return "";
    }

    private static boolean isEmpty(String s) {
        return s == null || s.isEmpty();
    }

    private static String checkFile() {
        if (isEmpty(sqlFileNameFrom)) {
            return "源文件名称不能为空";
        }
        if (!sqlFileNameFrom.endsWith(".sql")) {
            return "源文件不是sql文件";
        }
        File from = new File(sqlFileNameFrom);
        if (!from.exists()) {
            return "源文件不存在于工程根目录下";
        }
        if (isEmpty(sqlFileNameTo)) {
            return "目标文件名称不能为空";
        }
        if (!sqlFileNameTo.endsWith(".sql")) {
            return "目标文件不是sql文件";
        }
        File to = new File(sqlFileNameTo);
        if (!to.exists()) {
            return "目标文件不存在于工程根目录下";
        }
        return null;
    }

    private static String getFileContent(String fileName) {
        File file = new File(fileName);
        try (FileInputStream fis = new FileInputStream(file)) {
            byte[] b = new byte[(int) file.length()];
            fis.read(b);
            return new String(b, "UTF-8");
        } catch (IOException e) {
            e.printStackTrace();
        }
        return "";
    }

}
